package demo1;

import org.junit.Test;

import java.io.IOException;
import java.io.Serializable;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.text.ParseException;

/**
 * 通过转账测试事务
 */
public class TransationTest {


    // 转账的操作
    @Test
    public void test1() throws SQLException, ParseException, IOException {
        Connection connection = JDBCUtil.getConnection();
        try {
            connection.setAutoCommit(false);

            String sql1 = "update user_table set balance = balance - 100 where user = 'aa'";
            String sql2 = "update user_table set balance = balance + 100 where user = 'bb'";

            update2(connection,sql1);

            // 模拟网络阻塞
            System.out.println(5 / 0);

            final int rows = update2(connection, sql2);
            if (rows > 0) {
                System.out.println("转账成功");
            }
        }catch (Exception ex) {
            System.out.println("转账失败！");
            // 回滚事务的所有操作
            connection.rollback();
        }finally {
            // 事务提交
            connection.commit();
            JDBCUtil.closeConnection(connection,null);
        }

    }

    // 未考虑事务的版本：1.0
    private int update( String sql , Object...params) throws SQLException, ParseException, IOException {

         Connection connection = null;
         PreparedStatement preparedStatement = null;
        try {
           connection = JDBCUtil.getConnection();
           preparedStatement = connection.prepareStatement(sql);
           for (int i = 0; i < params.length; i++) {
               preparedStatement.setObject(i+1, params[i]);
           }
            return preparedStatement.executeUpdate();
        }catch (Exception ex) {
             ex.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(connection,preparedStatement);
        }
        return 0;
    }


    // 考虑事务的版本：2.0
    private int update2(Connection connection, String sql , Object...params) throws SQLException, ParseException, IOException {

        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1, params[i]);
            }
            return preparedStatement.executeUpdate();
        }catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            JDBCUtil.closeConnection(null,preparedStatement);
        }
        return 0;
    }


    /********************事务的演示*******************************/

    @Test
    public void select() throws SQLException, IOException, NoSuchFieldException, IllegalAccessException, InstantiationException {


        final Connection connection = JDBCUtil.getConnection();

        System.out.println(connection.getTransactionIsolation());

        // 关闭自动提交
        connection.setAutoCommit(false);

        String sql = "select * from user_table where name = ?";

        final User ccc = getInstance(connection, User.class, sql, "ccc");

        System.out.println(ccc);

        // TODO 暂时不关闭连接
//        connection.commit();
//        JDBCUtil.closeConnection(connection,null);

    }

    @Test
    public void update() throws SQLException, IOException, ParseException, InterruptedException {
        final Connection connection = JDBCUtil.getConnection();
        // 关闭自动提交
        connection.setAutoCommit(false);
        String sql = "update user_table set balance = ? where name = ?";
        final int ccc = update2(connection, sql, 8000, "ccc");
        if (ccc > 0) {
            System.out.println("修改成功");
        }

        Thread.sleep(1000 * 15);

        System.out.println("结果关闭");



        // TODO 暂时不关闭连接
//        connection.commit();
//        JDBCUtil.closeConnection(connection,null);

    }


    public <T> T getInstance(final Connection connection, Class<T> clazz, String sql , Object...params) throws SQLException, IOException, NoSuchFieldException, IllegalAccessException, InstantiationException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                if (params[i] == null) {
                    continue;
                }
                preparedStatement.setObject(i+1,params[i]);
            }
            resultSet = preparedStatement.executeQuery();
            // 拿到结果集的元数据，用于获取表的列数等信息
            final ResultSetMetaData metaData = resultSet.getMetaData();
            final int columnCount = metaData.getColumnCount();
            if (resultSet.next()) {
                // 泛型的实例只能通过运行时类来创建,创建实例，类型为T类型（泛型）
                final T t = clazz.newInstance();
                System.out.println(t.getClass());
                for (int i = 0; i < columnCount; i++) {
                    final Object columnValue = resultSet.getObject(i + 1);
                    // 获取列的列名，但获取不了列的别名
                    final String columnName = metaData.getColumnName(i + 1);
                    // 调用student对象的columnName列，设置为columnValue
                    final Field f1 = clazz.getDeclaredField(columnName);
                    f1.setAccessible(true);
                    f1.set(t,columnValue);
                }
                return t;
            }

        } finally {
            JDBCUtil.closeConnection(null,preparedStatement,resultSet);
        }
        return null;
    }




}
